﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.Common;

namespace Exam_notifier
{
    public class DBAccess
    {
        private DbProviderFactory DBProvFac;
        private DbConnection Connection;

        public DBAccess()
        {
            string DBProv = ConfigurationManager.AppSettings["provider"];
            string connectionString = ConfigurationManager.ConnectionStrings["myDB"].ConnectionString;
            DBProvFac = DbProviderFactories.GetFactory(DBProv);

            Connection = DBProvFac.CreateConnection();
            Connection.ConnectionString = connectionString;
            Connection.Open();
        }

        /// <summary>
        /// Insert new Student to database
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="Fullname"></param>
        /// <param name="DOB">Date of birth musts be in mm/dd/yyyy format</param>
        /// <returns>true if insert successfully, false otherwise</returns>
        public bool InsertStudent(string ID, string Fullname, string DOB)
        {
            return this.Insert("Students", ID, Fullname, DOB);
        }

        /// <summary>
        /// Insert new Subject to database
        /// </summary>
        /// <param name="SubjectName"></param>
        /// <param name="Type"></param>
        /// <param name="Fullname"></param>
        /// <returns>true if insert successfully, false otherwise</returns>
        public bool InsertSubject(string SubjectName, 
            string Type, string Fullname)
        {
            return this.Insert("Subjects", SubjectName, Type, Fullname);
        }

        /// <summary>
        /// Insert new Subject to database
        /// </summary>
        /// <param name="StudentID"></param>
        /// <param name="StartTime">Start time musts be in mm/dd/yyyy format</param>
        /// <param name="EndTime">End time musts be in mm/dd/yyyy format</param>
        /// <param name="Room"></param>
        /// <param name="Seat"></param>
        /// <param name="ExamType"></param>
        /// <param name="Subject"></param>
        /// <param name="Note"></param>
        /// <returns>true if insert successfully, false otherwise</returns>
        public bool InsertSchedule(string StudentID,
            string StartTime, string EndTime, string Room, string Seat,
            string ExamType, string Subject, string Note)
        {
            return this.Insert("Schedules", StudentID, StartTime,
                EndTime, Room, Seat, ExamType, Subject, Note);
        }

        /// <summary>
        /// Insert to any table with params of value
        /// </summary>
        /// <param name="table">Table's name to insert: Students, Subjects or Schedules</param>
        /// <param name="values">Params of value in order</param>
        /// <returns>true if insert successfully, false otherwise</returns>
        private bool Insert(string table, params string[] values)
        {
            DbCommand DBCmd = DBProvFac.CreateCommand();
            DBCmd.Connection = this.Connection;
            
            string sql = "";
            switch (table)
            { 
                case "Students":
                    string StudentID = values[0];
                    string StudentFullname = values[1];
                    string DOB = values[2];
                    sql = string.Format("INSERT INTO Students VALUES('{0}', '{1}', '{2}')",
                        StudentID, StudentFullname, DOB);
                    break;
                case "Subjects":
                    string SubjectName = values[0];
                    string Type = values[1];
                    string SubjectFullname = values[2];
                    sql = string.Format("INSERT INTO Subjects VALUES('{0}', '{1}','{2}')",
                        SubjectName, Type, SubjectFullname);
                    break;
                case "Schedules":
                    StudentID = values[0];
                    string StartTime = values[1];
                    string EndTime = values[2];
                    string Room = values[3];
                    string Seat = values[4];
                    string ExamType = values[5];
                    string SubjectID = values[6];
                    string Note = values[7];
                    sql = string.Format("INSERT INTO Subjects VALUES('{0}', '{1}','{2}', '{3}' " +
                        ", '{4}','{5}', '{6}', '{7}')", 
                        StudentID, StartTime, EndTime, Room, Seat, ExamType);
                    break;
            }

            DBCmd.CommandText = sql;

            try
            {
                int result = DBCmd.ExecuteNonQuery();
                if (result == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception e)
            {
                return false;
            }
        }
    }
}
